#!/usr/bin/perl

use DBI;
use Cwd;
use POSIX;
use strict;

# these hashes map column names in tables to their order
my %observation;
my %calibrators;
($observation{'source'},$observation{'ra'},$observation{'dec'},$observation{'n_if'},
 $observation{'if_freq'},$observation{'if_nchans'},$observation{'if_bw'},$observation{'time'},
 $observation{'arrayname'},$observation{'project_code'},$observation{'rpfits_file'},
 $observation{'cycle_time'},$observation{'offsource'},$observation{'onsource'},
 $observation{'scantype'},$observation{'calcode'},$observation{'stations'},
 $observation{'header_number'},$observation{'num_processed'},$observation{'processed_ids'},
 $observation{'id'},$observation{'az'},$observation{'el'},$observation{'attenmm'},
 $observation{'subrefl'},$observation{'corr_cfg'},$observation{'airtemp'},
 $observation{'chi'},$observation{'evector'},$observation{'jyperk'},
 $observation{'pntra'},$observation{'pntdec'},$observation{'relhumid'},
 $observation{'smonrms'},$observation{'wind'},$observation{'winddir'},
 $observation{'xtsys'},$observation{'ytsys'},$observation{'xyphase'},$observation{'xcaljy'},
 $observation{'ycaljy'},$observation{'xgtp'},$observation{'ygtp'},$observation{'xsdo'},
 $observation{'ysdo'},$observation{'pointcor'},$observation{'point_time'},
 $observation{'point_source'},$observation{'point_az'},$observation{'point_el'})=
    (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,
     31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49);
($calibrators{'name'},$calibrators{'ra'},$calibrators{'dec'},$calibrators{'type'},
 $calibrators{'low_freq'},$calibrators{'high_freq'},$calibrators{'bandpass_21cm'},
 $calibrators{'bandpass_13cm'},$calibrators{'bandpass_6cm'},$calibrators{'bandpass_3cm'},
 $calibrators{'bandpass_12mm'},$calibrators{'bandpass_7mm'},$calibrators{'bandpass_3mm'})=
    (50,51,52,53,54,55,56,57,58,59,60,61,62);

# database details
my $dsn="dbi:Pg:dbname=atca;host=mentok;port=5432";
my $user="postgres";
my $password="";

# Setup the database connection
my $dbh=DBI->connect($dsn,$user,$password,{ RaiseError => 0, AutoCommit => 1});

# open the .run file
my $run_file="uranus_1934_fixedmodel_calibration.run";
open(RUN,$run_file);
open(OUT,">".$run_file.".metadata");
while(<RUN>){
    chomp;
    my $line=$_;
    print OUT $line."\n";
    if ($line=~/\(ID\s\=\s(.*)\)$/){
	# get info about this ID
	my $this_id=$1;
	my @id_response=&get_id_info($dbh,$this_id);
	print OUT "array: ".$id_response[$observation{'arrayname'}];
	if ($id_response[$observation{'el'}]){
	    print OUT " (az,el)=(".$id_response[$observation{'az'}].",".
		$id_response[$observation{'el'}].") ".
		"(temp,humid,smonrms)=(".$id_response[$observation{'airtemp'}].",".
		$id_response[$observation{'relhumid'}].",".
		$id_response[$observation{'smonrms'}].") ".
		"point (time,az,el,source)=(".$id_response[$observation{'point_time'}].",".
		$id_response[$observation{'point_az'}].",".$id_response[$observation{'point_el'}].",".
		$id_response[$observation{'point_source'}].")";
	}
	print OUT "\n";
    }
}
close(RUN);
close(OUT);

sub get_id_info {
    my ($dbh,$get_id)=@_;

    my $get_query="SELECT * FROM observation WHERE id=".$get_id;
#    print "executing query: $get_query\n";
    my $get=$dbh->prepare($get_query);
    $get->execute;
    my @get_response;
    while (@get_response=$get->fetchrow){
	last;
    }
    $get->finish;

    return @get_response;
}
